create procedure sp_asi_NameProspectUnion
@ID varchar(15)='',
@LastFirst varchar(30)='',
@CompanySort varchar(30)='',
@MaxCount integer=0
as
set nocount on
if @ID<>''
-- Search by ID
BEGIN
select ID,PREFIX,LAST_FIRST,FIRST_NAME,MIDDLE_NAME,LAST_NAME,SUFFIX,FULL_NAME,TITLE,COMPANY,COMPANY_SORT,CITY,
STATE_PROVINCE,ZIP,COUNTRY,HOME_PHONE,WORK_PHONE,FAX,EMAIL,ORG_CODE,MEMBER_TYPE,CATEGORY,STATUS,MAJOR_KEY,
CO_ID,BT_ID,DUP_MATCH_KEY,FULL_ADDRESS,DESIGNATION,INFORMAL,TOLL_FREE,MAIL_CODE,CRRT,BAR_CODE,COUNTY,
MAIL_ADDRESS_NUM,BILL_ADDRESS_NUM,GENDER,BIRTH_DATE,US_CONGRESS,STATE_SENATE,STATE_HOUSE,SIC_CODE,CHAPTER,
FUNCTIONAL_TITLE,CONTACT_RANK,MEMBER_RECORD,COMPANY_RECORD,JOIN_DATE,SOURCE_CODE,PAID_THRU,MEMBER_STATUS,
MEMBER_STATUS_DATE,PREVIOUS_MT,MT_CHANGE_DATE,CO_MEMBER_TYPE,EXCLUDE_MAIL,EXCLUDE_DIRECTORY,DATE_ADDED,
LAST_UPDATED,UPDATED_BY,INTENT_TO_EDIT,ADDRESS_NUM_1,ADDRESS_NUM_2,ADDRESS_NUM_3,WEBSITE,'' ImisID,0 IsProspect
from Name where ID = @ID
UNION ALL
select ProspectID ID,Prefix PREFIX,LastFirst LAST_FIRST,FirstName FIRST_NAME,
MiddleName MIDDLE_NAME,LastName LAST_NAME,Suffix SUFFIX,FullName FULL_NAME,Title TITLE,
Organization COMPANY,CompanySort COMPANY_SORT,City CITY,StateProvince STATE_PROVINCE,
PostalCode IP,Country COUNTRY,HomePhone HOME_PHONE,WorkPhone WORK_PHONE,Fax FAX,Email EMAIL,
'' ORG_CODE,ListCode MEMBER_TYPE,
'' CATEGORY,'' STATUS,'' MAJOR_KEY,'' CO_ID,'' BT_ID,'' DUP_MATCH_KEY,'' FULL_ADDRESS,
'' DESIGNATION,'' NFORMAL,'' TOLL_FREE,'' MAIL_CODE,'' CRRT,'' BAR_CODE,'' COUNTY,
0 MAIL_ADDRESS_NUM,0 BILL_ADDRESS_NUM,'' GENDER,NULL BIRTH_DATE,'' US_CONGRESS,'' STATE_SENATE,
'' STATE_HOUSE,'' SIC_CODE,'' CHAPTER,'' FUNCTIONAL_TITLE,0 CONTACT_RANK,0 MEMBER_RECORD,
0 COMPANY_RECORD,NULL JOIN_DATE,'' SOURCE_CODE,NULL PAID_THRU,'' MEMBER_STATUS,NULL MEMBER_STATUS_DATE,
'' PREVIOUS_MT,NULL MT_CHANGE_DATE,'' CO_MEMBER_TYPE,0 EXCLUDE_MAIL,0 EXCLUDE_DIRECTORY,
NULL DATE_ADDED,NULL LAST_UPDATED,'' UPDATED_BY,'' INTENT_TO_EDIT,0 ADDRESS_NUM_1,
0 ADDRESS_NUM_2,0 ADDRESS_NUM_3,'' WEBSITE,ImisID,1 IsProspect from Prospect
where ProspectID = @ID
END
ELSE IF @LastFirst<>''
-- Search by LastFirst
BEGIN
set rowcount @MaxCount
select ID,PREFIX,LAST_FIRST,FIRST_NAME,MIDDLE_NAME,LAST_NAME,SUFFIX,FULL_NAME,TITLE,COMPANY,COMPANY_SORT,CITY,
STATE_PROVINCE,ZIP,COUNTRY,HOME_PHONE,WORK_PHONE,FAX,EMAIL,ORG_CODE,MEMBER_TYPE,CATEGORY,STATUS,MAJOR_KEY,
CO_ID,BT_ID,DUP_MATCH_KEY,FULL_ADDRESS,DESIGNATION,INFORMAL,TOLL_FREE,MAIL_CODE,CRRT,BAR_CODE,COUNTY,
MAIL_ADDRESS_NUM,BILL_ADDRESS_NUM,GENDER,BIRTH_DATE,US_CONGRESS,STATE_SENATE,STATE_HOUSE,SIC_CODE,CHAPTER,
FUNCTIONAL_TITLE,CONTACT_RANK,MEMBER_RECORD,COMPANY_RECORD,JOIN_DATE,SOURCE_CODE,PAID_THRU,MEMBER_STATUS,
MEMBER_STATUS_DATE,PREVIOUS_MT,MT_CHANGE_DATE,CO_MEMBER_TYPE,EXCLUDE_MAIL,EXCLUDE_DIRECTORY,DATE_ADDED,
LAST_UPDATED,UPDATED_BY,INTENT_TO_EDIT,ADDRESS_NUM_1,ADDRESS_NUM_2,ADDRESS_NUM_3,WEBSITE,'' ImisID,0 IsProspect
from Name where LAST_FIRST like @LastFirst+'%'
UNION ALL
select ProspectID ID,Prefix PREFIX,LastFirst LAST_FIRST,FirstName FIRST_NAME,
MiddleName MIDDLE_NAME,LastName LAST_NAME,Suffix SUFFIX,FullName FULL_NAME,Title TITLE,
Organization COMPANY,CompanySort COMPANY_SORT,City CITY,StateProvince STATE_PROVINCE,
PostalCode IP,Country COUNTRY,HomePhone HOME_PHONE,WorkPhone WORK_PHONE,Fax FAX,Email EMAIL,
'' ORG_CODE,ListCode MEMBER_TYPE,
'' CATEGORY,'' STATUS,'' MAJOR_KEY,'' CO_ID,'' BT_ID,'' DUP_MATCH_KEY,'' FULL_ADDRESS,
'' DESIGNATION,'' NFORMAL,'' TOLL_FREE,'' MAIL_CODE,'' CRRT,'' BAR_CODE,'' COUNTY,
0 MAIL_ADDRESS_NUM,0 BILL_ADDRESS_NUM,'' GENDER,NULL BIRTH_DATE,'' US_CONGRESS,'' STATE_SENATE,
'' STATE_HOUSE,'' SIC_CODE,'' CHAPTER,'' FUNCTIONAL_TITLE,0 CONTACT_RANK,0 MEMBER_RECORD,
0 COMPANY_RECORD,NULL JOIN_DATE,'' SOURCE_CODE,NULL PAID_THRU,'' MEMBER_STATUS,NULL MEMBER_STATUS_DATE,
'' PREVIOUS_MT,NULL MT_CHANGE_DATE,'' CO_MEMBER_TYPE,0 EXCLUDE_MAIL,0 EXCLUDE_DIRECTORY,
NULL DATE_ADDED,NULL LAST_UPDATED,'' UPDATED_BY,'' INTENT_TO_EDIT,0 ADDRESS_NUM_1,
0 ADDRESS_NUM_2,0 ADDRESS_NUM_3,'' WEBSITE,ImisID,1 IsProspect from Prospect
where LastFirst like @LastFirst+'%' and ImisID=''
END
ELSE
-- Search by CompanySort
BEGIN
set rowcount @MaxCount
select ID,PREFIX,LAST_FIRST,FIRST_NAME,MIDDLE_NAME,LAST_NAME,SUFFIX,FULL_NAME,TITLE,COMPANY,COMPANY_SORT,CITY,
STATE_PROVINCE,ZIP,COUNTRY,HOME_PHONE,WORK_PHONE,FAX,EMAIL,ORG_CODE,MEMBER_TYPE,CATEGORY,STATUS,MAJOR_KEY,
CO_ID,BT_ID,DUP_MATCH_KEY,FULL_ADDRESS,DESIGNATION,INFORMAL,TOLL_FREE,MAIL_CODE,CRRT,BAR_CODE,COUNTY,
MAIL_ADDRESS_NUM,BILL_ADDRESS_NUM,GENDER,BIRTH_DATE,US_CONGRESS,STATE_SENATE,STATE_HOUSE,SIC_CODE,CHAPTER,
FUNCTIONAL_TITLE,CONTACT_RANK,MEMBER_RECORD,COMPANY_RECORD,JOIN_DATE,SOURCE_CODE,PAID_THRU,MEMBER_STATUS,
MEMBER_STATUS_DATE,PREVIOUS_MT,MT_CHANGE_DATE,CO_MEMBER_TYPE,EXCLUDE_MAIL,EXCLUDE_DIRECTORY,DATE_ADDED,
LAST_UPDATED,UPDATED_BY,INTENT_TO_EDIT,ADDRESS_NUM_1,ADDRESS_NUM_2,ADDRESS_NUM_3,WEBSITE,'' ImisID,0 IsProspect
from Name where COMPANY_SORT like @CompanySort+'%'
UNION ALL
select ProspectID ID,Prefix PREFIX,LastFirst LAST_FIRST,FirstName FIRST_NAME,
MiddleName MIDDLE_NAME,LastName LAST_NAME,Suffix SUFFIX,FullName FULL_NAME,Title TITLE,
Organization COMPANY,CompanySort COMPANY_SORT,City CITY,StateProvince STATE_PROVINCE,
PostalCode IP,Country COUNTRY,HomePhone HOME_PHONE,WorkPhone WORK_PHONE,Fax FAX,Email EMAIL,
'' ORG_CODE,ListCode MEMBER_TYPE,
'' CATEGORY,'' STATUS,'' MAJOR_KEY,'' CO_ID,'' BT_ID,'' DUP_MATCH_KEY,'' FULL_ADDRESS,
'' DESIGNATION,'' NFORMAL,'' TOLL_FREE,'' MAIL_CODE,'' CRRT,'' BAR_CODE,'' COUNTY,
0 MAIL_ADDRESS_NUM,0 BILL_ADDRESS_NUM,'' GENDER,NULL BIRTH_DATE,'' US_CONGRESS,'' STATE_SENATE,
'' STATE_HOUSE,'' SIC_CODE,'' CHAPTER,'' FUNCTIONAL_TITLE,0 CONTACT_RANK,0 MEMBER_RECORD,
0 COMPANY_RECORD,NULL JOIN_DATE,'' SOURCE_CODE,NULL PAID_THRU,'' MEMBER_STATUS,NULL MEMBER_STATUS_DATE,
'' PREVIOUS_MT,NULL MT_CHANGE_DATE,'' CO_MEMBER_TYPE,0 EXCLUDE_MAIL,0 EXCLUDE_DIRECTORY,
NULL DATE_ADDED,NULL LAST_UPDATED,'' UPDATED_BY,'' INTENT_TO_EDIT,0 ADDRESS_NUM_1,
0 ADDRESS_NUM_2,0 ADDRESS_NUM_3,'' WEBSITE,ImisID,1 IsProspect from Prospect
where CompanySort like @CompanySort+'%' and ImisID=''
END

GO
GRANT EXECUTE ON  [dbo].[sp_asi_NameProspectUnion] TO [IMIS]
GO